# 数据库 系统
from sqlalchemy import create_engine                        # 数据库连接
from sqlalchemy.ext.declarative import declarative_base   # 基类

from sqlalchemy.orm import sessionmaker    # 数据插入
from sqlalchemy import Column, String, Integer, Boolean, DateTime,ForeignKey, Float, TIMESTAMP, BigInteger, Enum, func
from sqlalchemy.orm import sessionmaker, relationship, query
from db import conn_db_conf
import datetime

# 表结构
user_name = conn_db_conf.user_name
password = conn_db_conf.password
location = conn_db_conf.location
db_name = conn_db_conf.db_name

# 连接数据库
# engine = create_engine("mysql+pymysql://root:root@localhost/news", encoding='utf-8', echo=True) #echo>打印所有  engine相当于socket
conn_db_str = "mysql+pymysql://%s:%s@%s/%s?charset=utf8" % (user_name, password, location, db_name)
# engine = create_engine("mysql+pymysql://qr:root@localhost/qrSQL?charset=utf8") #echo>打印所有  engine相当于socket  指定中文插入方法

engine = create_engine(conn_db_str)  # echo>打印所有  engine相当于socket  指定中文插入方法

# 基类 创建表
Base = declarative_base()

class OCR_SCAN_LOG(Base):
    __tablename__ = 'ocr_scan_log'                                       # 表名
    # id = Column(Integer, primary_key=True, autoincrement=True)          # id
    RMC_bar = Column(String(64), nullable=False, primary_key=True)    # RMC扫码
    BOX = Column(Integer, nullable=False, default=0)         # 箱数
    serial_number = Column(BigInteger, nullable=False, primary_key=True)  # 唯一码  栈板标识号
    error = Column(String(128), nullable=False, default='')  # 异常说明

    def __repr__(self):
        return "[RMC_bar:{0}, BOX:{1}, number{2}, error{3}".\
            format(self.RMC_bar, self.BOX, self.serial_number, self.error)

# AS400同步数据
class AS400_SYNC(Base):
    __tablename__ = 'as400_sync'  # 表名
    id = Column(Integer, primary_key=True, autoincrement=True)
    RMC_bar = Column(String(64), nullable=False)    # RMC扫码
    ticket_bar = Column(Integer, nullable=False)         # 出张出库票
    plant = Column(Integer, nullable=False)            # 厂家
    data = Column(Integer, nullable=False)               # 日期
    time = Column(Integer, nullable=False)               # 时间
    demand = Column(Integer, nullable=False)             # 需求数
    BOX = Column(Integer, nullable=False)                # 胶箱数
    BOX_num = Column(Integer, nullable=False)            # 箱入数
    REQ = Column(Integer, nullable=False)                # 指示数量
    serial_num = Column(Integer, nullable=False, default=0)         # 序号(serial_num)
    invoice_time = Column(Integer, nullable=False)       # 发票时间(invoice_time)
    area_code = Column(String(24), nullable=False)          # 区域代码
    lineName = Column(String(24), nullable=False)           # 线名
    station = Column(String(24), nullable=False)            # 工位
    update_time = Column(DateTime, nullable=False)          # 更新时间

    def __repr__(self):
        return "[RMC_bar:{0}, ticket_bar:{1}, plant:{2}, data:{3}, time:{4}, demand:{5}, BOX:{6}, BOX_num:{7}," \
               " REQ:{8}, serial_num:{9}, invoice_time:{10}, area_code:{11}, lineName:{12}, station:{13}], update_time:{14}".\
            format(self.RMC_bar, self.ticket_bar, self.plant, self.data, self.time,
                   self.demand, self.BOX, self.BOX_num, self.REQ,self.serial_num,
                   self.invoice_time, self.area_code, self.lineName, self.station, self.update_time)

# 出张出库票数据
class OUT_TICKET(Base):
    __tablename__ = 'out_ticket'                                   # 表名
    # id = Column(Integer, primary_key=True)                          # id
    RMC_bar = Column(String(64), nullable=False, primary_key=True)                    # RMC扫码
    ticket_bar = Column(Integer, nullable=False)                    # 出张出库票
    plant = Column(Integer, nullable=False)                         # 厂家
    data = Column(Integer, nullable=False)                          # 日期
    time = Column(Integer, nullable=False)                          # 时间
    demand = Column(Integer, nullable=False)                        # 需求数
    BOX = Column(Integer, nullable=False)                           # 胶箱数
    BOX_num = Column(Integer, nullable=False)                       # 箱入数
    REQ = Column(Integer, nullable=False)                           # 指示数量
    serial_num = Column(Integer, nullable=False, default=0)         # 序号(serial_num)
    invoice_time = Column(Integer, nullable=False)                  # 发票时间(invoice_time)
    area_code = Column(String(24), nullable=False)                  # 区域代码
    lineName = Column(String(24), nullable=False)                   # 线名
    station = Column(String(24), nullable=False)                    # 工位

    is_alter = Column(Boolean, nullable=False, default=0, server_default='0')                   # 出张出库票是否有改动  0没有 1 有
    alter_item = Column(String(24), nullable=False, default='', server_default='')                 # 修改项目值
    serial_number = Column(BigInteger, nullable=False, primary_key=True)              # 唯一码  栈板标识号
    is_submit = Column(Boolean, nullable=False, default=0, server_default='0')      # 是否有提交AS400  提交1, 没提交0
    AS400_return = Column(String(128), nullable=False, default='', server_default='')               # AS400提交返回异常返回
    submit_count = Column(Integer, nullable=False, default=0, server_default='0')           # 提交次数统计
    is_pass = Column(Boolean, nullable=False, default=0, server_default='0')        # 是否已经通过AS400认证  pass  0: 没通过  1: 为通过
    result = Column(Boolean, nullable=False, default='1', server_default='1')           # 第一次本地验证状态 1: 无异常 0 有异常待处理
    error = Column(String(128), nullable=False, default='', server_default='')                      # 异常说明

    def __repr__(self):
        return "[RMC_bar:{0}, ticket_bar:{1}, plant:{2}, data:{3}, time:{4}, demand:{5}, BOX:{6}, BOX_num:{7}," \
               " REQ:{8}, serial_num:{9}, invoice_time:{10}, area_code:{11}, lineName:{12}, station:{13}" \
               " is_alter:{14}, alter_item:{15}, serial_number:{16}, is_submit:{17}," \
               " AS400_return:{18}, submit_count:{19}, is_pass:{20}, result:{21}, error:{22}]".\
                format(self.RMC_bar, self.ticket_bar, self.plant, self.data, self.time,
                       self.demand, self.BOX, self.BOX_num, self.REQ,self.serial_num,
                       self.invoice_time, self.area_code, self.lineName, self.station,
                       self.is_alter, self.alter_item, self.serial_number,
                       self.is_submit, self.AS400_return, self.submit_count,
                       self.is_pass, self.result, self.error)

# Y1DTTM,Y1DTTP,Y1MP,Y1SQII,Y1SQID,Y1FQII,Y1FQID,Y1FQPK,Y1ISTY,Y1SECP,Y1ACDE,Y1QISI,Y1BOXI,Y1BOXF,Y1QISF,Y1USED,Y1USET,Y1SETI,Y1XISS,Y1INPN,Y1STKN,Y1XLOC,Y1IT,Y1SD,Y1SP,Y1PS,Y1ITOH,Y1PCK,Y1PCKQ,Y1NOOV,Y1XTIV,Y1CON,Y1PLT,Y1TRD,Y1TRT,Y1FLAG,Y1XOPA,Y1XOPB,Y1XOPC,Y1XOPD,Y1XOPE,Y1XOPF,Y1XOPG,Y1RSV1,Y1RSV2,Y1RSV3
# [(20210610113599, 1, )]
class OCR(Base):
    __tablename__ = 'ocr'
    id = Column(Integer, primary_key=True, autoincrement=True)
    # Y1DTTM = Column(BigInteger, nullable=False, primary_key=True)         # 数据作成时间+连番  唯一 KEY
    Y1DTTM = Column(String(128), nullable=False, primary_key=True)         # 数据作成时间+连番  唯一 KEY
    Y1DTTP = Column(Integer, nullable=False)                              # 数据类型
    Y1MP = Column(String(4), nullable=False)                              # 工厂
    Y1SQII = Column(Integer, nullable=False)                              # 指示大票号
    Y1SQID = Column(Integer, nullable=False)                              # 大票顺序号
    Y1FQII = Column(Integer, nullable=False)                           # 小票1
    Y1FQID = Column(Integer, nullable=False)                           # 小票2
    Y1FQPK = Column(Integer, nullable=False)                           # 小票3
    Y1ISTY = Column(String(12), nullable=False)                           # 指示类型
    Y1SECP = Column(Integer, nullable=False)                           # 供应商
    Y1ACDE = Column(String(12), nullable=False)                           # 区域代码
    Y1QISI = Column(Integer, nullable=False)                           # 净需求数量
    Y1BOXI = Column(Integer, nullable=False)                           # 指示数量
    Y1BOXF = Column(Integer, nullable=False)                           # 供应商交货数
    Y1QISF = Column(Integer, nullable=False)                           # 收货总数量
    Y1USED = Column(Integer, nullable=False)                           # 使用日期
    Y1USET = Column(Integer, nullable=False)                           # 使用时间
    Y1SETI = Column(String(16), nullable=False)                           # 生产线
    Y1XISS = Column(String(16), nullable=False)                           # 工位
    Y1INPN = Column(Integer, nullable=False)                           # OCR号码
    Y1STKN = Column(String(16), nullable=False)                           # 担当号
    Y1XLOC = Column(String(16), nullable=False)                           # 帽子/台车号
    Y1IT = Column(String(16), nullable=False)                             # 部番
    Y1SD = Column(String(16), nullable=False)                             # 设变后缀
    Y1SP = Column(String(16), nullable=False, default='', server_default='')        # 工程后缀
    Y1PS = Column(String(16), nullable=False)                               # 工程顺序
    Y1ITOH = Column(String(36), nullable=False)                             # 部番合并
    Y1PCK = Column(Float, nullable=False, default=0.0, server_default='0.0')                               # 箱号
    Y1PCKQ = Column(Float, nullable=False, default=0.0, server_default='0.0')                               # 箱内实际数
    Y1NOOV = Column(String(26), nullable=False, default='', server_default='')       # 订单
    Y1XTIV = Column(Float, nullable=False, default=0.0, server_default='0.0')       # PATERN
    Y1CON = Column(String(24), nullable=False, default='', server_default='')       # 货柜号
    Y1PLT = Column(String(20), nullable=False, default='', server_default='')       # 卡板号
    Y1TRD = Column(Integer, nullable=False, default=0, server_default='0')       # 处理日期
    Y1TRT = Column(Integer, nullable=False, default=0, server_default='0')       # 处理时间
    Y1FLAG = Column(String(16), nullable=False, default='', server_default='')       # 数据状态标识
    Y1XOPA = Column(String(16), nullable=False, default='', server_default='')       # 错误类型1（部品要检查）
    Y1XOPB = Column(String(16), nullable=False, default='', server_default='')       # 错误类型2（PO不足）
    Y1XOPC = Column(String(16), nullable=False, default='', server_default='')       # 错误类型3（无单重）
    Y1XOPD = Column(String(16), nullable=False, default='', server_default='')       # 错误类型4（海关未备案）
    Y1XOPE = Column(String(16), nullable=False, default='', server_default='')       # 错误类型5（少来货）
    Y1XOPF = Column(String(16), nullable=False, default='', server_default='')       # 错误类型6（多来货）
    Y1XOPG = Column(String(16), nullable=False, default='', server_default='')       # 错误类型7
    Y1RSV1 = Column(String(30), nullable=False, default='', server_default='')       # 保留字段1
    Y1RSV2 = Column(String(40), nullable=False, default='', server_default='')       # 保留字段2
    Y1RSV3 = Column(String(20), nullable=False, default='', server_default='')       # 保留字段3

# RMC扫描记录
class SCAN_RMC(Base):
    __tablename__ = 'scan_rmc'  # 表名
    serial_number = Column(BigInteger, nullable=False, primary_key=True)  # 唯一码  栈板标识号
    RMC_bar = Column(String(128), nullable=False, primary_key=True)  # RMC扫码
    submit_time = Column(TIMESTAMP, nullable=True)

class SCAN_Y1SQII(Base):
    __tablename__ = 'scan_y1sqii'  # 表名
    serial_number = Column(BigInteger, nullable=False, primary_key=True)  # 唯一码  栈板标识号
    submit_time = Column(TIMESTAMP, nullable=True)








# 创建表
def create_Table():
    Base.metadata.create_all(engine)  # 创建表结构  父类调用子类,, 所有子类

# 删除
def drop_Table():
    Base.metadata.drop_all(engine)

create_Table() # 创建表
# drop_Table()   # 删除表
#
# Sess = sessionmaker(bind=engine)  # 数据插入  光标
# Session = Sess()    # 建立光标对象



#
# new_obj = OCR_SCAN_LOG(
#     RMC_bar="D2421231-+-P",  # RMC扫码
#     serial_number=4589009876543253  # 唯一码  栈板标识号
# )
#
# Session.add(new_obj)
# Session.commit()


#
# if __name__=='__main__':
#     import pandas as pd
#     Base = declarative_base()
#     DBSession = sessionmaker(bind=engine)
#     session = DBSession()
#
#     # 查询OCR表  正向查询
#     OCR_obj = session.query(OCR_SCAN_LOG).filter_by(RMC_bar="M0AJ1235D-+-P").first()
#     # 通过OCR表关联的relationship字段"Company"查询出company表的数据
#     print(OCR_obj.error)
#
#     # 反向查询
#     ERROR_obj = session.query(ERROR).filter_by(code="110").first()
#     print(ERROR_obj)
#     print(ERROR_obj.OCR_of_errorCode[0].RMC_bar)


    # print(phone_obj.company.name)
    # print(phone_obj.company.location)

    # class Company(Base):
    #     __tablename__ = "company"
    #     name = Column(String(20), primary_key=True)
    #     location = Column(String(20))
    #
    #     def __repr__(self):
    #         return "name:{0} location:{1}".format(self.name, self.location)
    #
    # class Phone(Base):
    #     __tablename__ = "phone"
    #     id = Column(Integer, primary_key=True)
    #     model = Column(String(32))
    #     price = Column(String(32))
    #     company_name = Column(String(32), ForeignKey("company.name"))
    #     company = relationship("Company", backref="phone_of_company")
















